package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.entity.Note;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Set;

/**
 * 笔记dao
 * Created by wyh on 2015/3/19.
 */
@Repository
public class NoteDao extends BaseDao {

    /**
     * 分页查询笔记列表
     *
     * @param groupId     群组ID
     * @param lessonId    课程ID
     * @param isOpen      1公开 0所有
     * @param page        页码
     * @param pageSize    每页条数
     * @param keyword     关键字
     * @param chapterId   章ID
     * @param nodeId      节ID
     * @param favoriteSet 收藏的集合
     * @return Page
     * @author wyh
     * @since 2015-3-19
     */
    public Page<Note> getNoteList(long userId, int role, int isOpen, long groupId, long lessonId, int page, int pageSize, String keyword, long chapterId, long nodeId, Set<Long> favoriteSet) {
        StringBuilder sql = new StringBuilder().append("select n.*, c.sName sChapter, s.sName sNode, cs.sName sLessonName from t_note n left join t_chapter c on c.id=n.iChapterId ")
                .append("left join t_chapter s ON s.id=n.iSectionId left join t_course cs on cs.id=n.iCourseId ");
        StringBuilder countSql = new StringBuilder("SELECT COUNT(*) FROM t_note n ");
        StringBuilder where = new StringBuilder(" WHERE 0=0 ");
        MapSqlParameterSource sps = new MapSqlParameterSource();

        //筛选创建者
        if (userId > 0) {
            log.debug("创建者ID不为0：userId > 0");
            where.append(" AND n.iCreatorId = :userId AND n.tiRole = :role ");
            sps.addValue("userId", userId).addValue("role", role);
        }

        //筛选公开
        if (isOpen > 0){
            log.debug("查询公开的笔记：isOpen > 0");
            where.append(" AND n.tiOpen = :isOpen ");
            sps.addValue("isOpen", isOpen);
        }

        //筛选课程
        if (lessonId > 0) {
            log.debug("课程ID不为0：lessonId > 0");
            where.append(" AND n.iCourseId = :lessonId ");
            sps.addValue("lessonId", lessonId);
            //筛选群组
            if (groupId > 0) {
                log.debug("课程群组ID不为0：groupId > 0");
                where.append(" AND (n.sCourseCode = (SELECT sCode FROM t_course_group WHERE id = :groupId) OR n.sCourseCode = '') ");
                sps.addValue("groupId", groupId);
            }
        }

        //筛选章
        if (chapterId > 0) {
            log.debug("章ID不为0：chapterId > 0");
            where.append(" AND n.iChapterId = :chapterId ");
            sps.addValue("chapterId", chapterId);
        }

        //筛选节
        if (nodeId > 0) {
            log.debug("节ID不为0：nodeId > 0");
            where.append(" AND n.iSectionId = :nodeId ");
            sps.addValue("nodeId", nodeId);
        }

        //筛选关键字
        if (!StringUtils.isEmpty(keyword)) {
            log.debug("关键字不为空：!StringUtils.isEmpty(keyword)");
            where.append(" AND UPPER(n.sContent) LIKE :keyword ");
            sps.addValue("keyword", "%" + keyword.toUpperCase() + "%");
        }

        //筛选收藏的集合
        if (favoriteSet != null) {
            //如果为空说明没有收藏任何，直接返回空页
            if (favoriteSet.isEmpty()) {
                log.debug("收藏笔记ID集合为空：favoriteSet.isEmpty()");
                return new Page<>();
            }
            where.append(" AND n.id IN(:ids) ");
            sps.addValue("ids", favoriteSet);
        }

        //查询总数
        int total = namedParameterJdbcTemplate.queryForObject(countSql.append(where).toString(), sps, Integer.class);
        if (total == 0) {
            log.debug("查询总数为0：total == 0");
            return new Page<>();
        }

        //LIMIT
        sql.append(where).append(" ORDER BY n.dtCreate DESC ").append(getLimitStr(page, pageSize));

        //查询
        List<Note> noteList = namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Note>() {
            @Override
            public Note mapRow(ResultSet rs, int rowNum) throws SQLException {
                Note note = new Note();
                note.setId(rs.getLong("id"));
                note.setCreatorId(rs.getLong("iCreatorId"));
                note.setRole(rs.getInt("tiRole"));
                note.setDate(rs.getTimestamp("dtCreate"));
                note.setContent(rs.getString("sContent"));
                note.setIsOpen(rs.getInt("tiOpen"));
                note.setChapter(rs.getString("sChapter"));
                note.setNode(rs.getString("sNode"));
                note.setLessonName(rs.getString("sLessonName"));
                note.setBest(1); //此处将收藏set为1，不必再循环收藏列表
                return note;
            }
        });

        log.debug("返回 笔记列表");
        return new Page<>(total, pageSize, page, noteList);
    }

    /**
     * 公开笔记
     *
     * @param noteId 笔记ID
     * @param open   公开
     * @return int
     * @author wyh
     * @since 2015-3-19
     */
    public int updateOpen(long noteId, int open) {
        String sql = "UPDATE t_note SET tiOpen = :open WHERE id = :noteId";
        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("noteId", noteId)
                .addValue("open", open);

        log.debug("返回 公开笔记的结果");
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 删除笔记
     *
     * @param noteId 笔记ID
     * @return int
     * @author wyh
     * @since 2015-3-20
     */
    public int deleteNote(long noteId) {
        String sql = "DELETE FROM t_note WHERE id = :noteId";
        SqlParameterSource sps = new MapSqlParameterSource().addValue("noteId", noteId);

        log.debug("返回 删除笔记的结果");
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 查询笔记明细
     * @param noteId 笔记ID
     * @return Note
     *
     * @author wyh
     * @since 2014-3-20
     */
    public Note getNote(long noteId) {
        String sql = "SELECT c.sName sCourseName, ch.sName sChapter, node.sName sNode, sContent, dtCreate, tiOpen " +
                "FROM t_note JOIN t_course c ON iCourseId = c.id LEFT JOIN t_chapter ch ON iChapterId = ch.id " +
                "LEFT JOIN t_chapter node ON iSectionId = node.id WHERE t_note.id = :noteId";

        SqlParameterSource sps = new MapSqlParameterSource().addValue("noteId", noteId);

        try {
            log.debug("返回 查询笔记");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, new RowMapper<Note>() {
                @Override
                public Note mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Note note = new Note();
                    note.setLessonName(rs.getString("sCourseName"));
                    note.setChapter(rs.getString("sChapter"));
                    note.setNode(rs.getString("sNode"));
                    note.setContent(rs.getString("sContent"));
                    note.setDate(rs.getTimestamp("dtCreate"));
                    note.setIsOpen(rs.getInt("tiOpen"));
                    return note;
                }
            });
        } catch (EmptyResultDataAccessException ex) {
            log.debug("查询笔记为空");
            return null;
        }
    }

    /**
     * 新增笔记
     * @param userId 用户ID
     * @param role 角色
     * @param note 笔记
     * @return int
     *
     * @author wyh
     * @since 2015-3-20
     */
    public int saveNote(long userId, int role, Note note){
        String sql = "INSERT INTO t_note VALUES(NULL, :sContent, :iCourseId, '', :iChapterId, :iSectionId, :iCreatorId, NOW(), :tiRole, :tiOpen)";
        //如果群组ID不为0，需要insert课程代码
        if(note.getGroupId() != 0){
            log.debug("新增笔记群组ID不为0：note.getGroupId() != 0");
            sql = "INSERT INTO t_note (SELECT NULL, :sContent, :iCourseId, sCode, :iChapterId, :iSectionId, :iCreatorId, NOW(), :tiRole, :tiOpen FROM t_course_group WHERE id = :iGroupId)";
        }
        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("sContent", note.getContent())
                .addValue("iCourseId", note.getCourseId())
                .addValue("iChapterId", note.getChapterId())
                .addValue("iSectionId", note.getSectionId())
                .addValue("iCreatorId", userId)
                .addValue("tiRole", role)
                .addValue("tiOpen", note.getIsOpen())
                .addValue("iGroupId", note.getGroupId());

        log.debug("返回 新增笔记结果");
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    /**
     * 检查笔记是否自己创建的
     *
     * @param userId 用户ID
     * @param role   角色
     * @param noteId 笔记ID
     * @return boolean
     * @author wyh
     * @since 2015-3-19
     */
    public boolean checkSelfNote(long userId, int role, long noteId) {
        String sql = "SELECT COUNT(*) FROM t_note WHERE iCreatorId = :userId AND tiRole = :role AND id = :noteId";
        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("userId", userId)
                .addValue("role", role)
                .addValue("noteId", noteId);

        log.debug("返回 笔记是否自己创建的");
        return namedParameterJdbcTemplate.queryForObject(sql, sps, Integer.class) > 0;
    }

    /**
     * 检查笔记属于教师负责的群组
     *
     * @param noteId    笔记ID
     * @param teacherId 教师ID
     * @return boolean
     * @author wyh
     * @since 2015-3-20
     */
    public boolean isInGroup(long noteId, long teacherId) {
        String sql = "SELECT COUNT(*) FROM t_course_group cg JOIN t_teacher t ON sTeacherNum=sNumber " +
                "JOIN t_note n ON sCode=sCourseCode WHERE t.id=:teacherId AND n.id=:noteId";

        SqlParameterSource sps = new MapSqlParameterSource()
                .addValue("teacherId", teacherId)
                .addValue("noteId", noteId);

        log.debug("返回 笔记是否属于教师负责的群组");
        return namedParameterJdbcTemplate.queryForObject(sql, sps, Integer.class) > 0;
    }
}
